package io.renren.dao;

import io.renren.common.dao.BaseDao;
import io.renren.dto.*;
import io.renren.entity.FinanceEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * 财务记录表
 *
 * @author 翟文闯 619556390@QQ.com
 * @since 1.0.0 2019-12-04
 */
@Mapper
public interface FinanceDao extends BaseDao<FinanceEntity> {
    @Select("SELECT * FROM canteens_finance m where  type != 4  ${status}  ${supplierId} ${bgeinDate} ${endDate}")
    List<FinanceDTO> selectFinance(String status, String supplierId, String bgeinDate,String endDate);

    @Select("SELECT count(*)as totalNum FROM canteens_finance  where  1=1 ")
    FinanceDTO select1Finance();
    @Select("SELECT count(*)as ytotalNum FROM canteens_finance where  1=1 and `is_payment` != 0 ")
    FinanceDTO select2Finance();
    @Select("SELECT count(*)as ntotalNum FROM canteens_finance where  1=1 and `is_payment` = 0 ")
    FinanceDTO select3Finance();
    @Select("SELECT * FROM canteens_finance where finance_id = '${order}'  ")
    FinanceDTO selectActualAmount(Long order);
    @Select("SELECT COUNT( fe.supplier_id)as orderNum,  B.supplier_name, fe.* from canteens_finance fe , canteens_supplier B  where  fe.supplier_id = B.supplier_id  ${isPayment}  ${type}  GROUP BY fe.supplier_id  ")
    List<FinanceDTO> selectSupplierFinance(String isPayment,String type);
    //通过唯一订单号获取创建时间，创建人，审核人，并关联出 名字
    @Select("select m.box_id,m.create_date,m.ched_id,(select real_name from sys_user a where a.id =  m.ched_id)chedName,m.canteen_admin_id, " +
            " (select a.supplier_name from canteens_supplier a where a.supplier_id ='${supplierId}') as supplier_name, " +
            "(select real_name from sys_user a where a.id =  m.canteen_admin_id)canteenAdminName ,m.audit_time " +
            " from canteens_small_canteen_purchase m where m.small_canteen_purchase_id = '${order}'  ")
    FinancemxDTO getsmallCanteenOrder(Long order);//, String bgeinDate,String endDate,Long supplierId
    @Select("SELECT  m.meal_time as create_date,m.ched_id,(select real_name from sys_user a where a.id = m.ched_id)chedName, " +
            "  (select a.supplier_name from canteens_supplier a where a.supplier_id ='${supplierId}') as supplier_name, " +
            "m.canteen_admin_id,(select real_name from sys_user a where a.id =  m.canteen_admin_id)canteenAdminName ,m.audit_time " +
            "from canteens_stock_order m where m.stock_order_id = '${order}' ")
    FinancemxDTO getStockOrder(Long order);
    @Select("SELECT m.create_date,m.ched_id,(select real_name from sys_user a where a.id =  m.ched_id)chedName, " +
            " (select a.supplier_name from canteens_supplier a where a.supplier_id ='${supplierId}') as supplier_name, " +
            "(select real_name from sys_user a where a.id = m.canteen_admin_id)canteenAdminName,m.canteen_admin_id ,m.audit_time " +
            "from  canteens_auxiliary_materi m where m.auxiliary_materials_id = '${order}'")
    FinancemxDTO getAuxiliaryOrder(Long order);
  //  根据状态0未结算 或者1已结算 查询订单详情商品名，单价，数量，单位
    @Select("select s.food_id,s.arrival_num,s.unit_price,f.food_name,f.unit from canteens_stock_ordermx s,canteens_food f where s.food_id =f.food_id and " +
            "s.supplier_id ='${supplierId}' and s.stock_order_id = '${order}'" )
    List<FinancemxDTO> getStockmxOrder(Long supplierId,Long order);
    @Select("select s.food_id,s.arrival_num,s.unit_price,f.food_name,f.unit from canteens_small_canteen_purchasemx s ,canteens_food f where s.food_id =f.food_id and   " +
            "s.supplier_id ='${supplierId}' and s.small_canteen_purchase_id = '${order}' ")
    List<FinancemxDTO> getsmallCanteenmxOrder(Long supplierId,Long order);
    @Select("select s.food_id,s.arrival_num,s.unit_price,f.food_name,f.unit from canteens_auxiliary_materialsmx s ,canteens_food f where s.food_id =f.food_id and " +
            "s.supplier_id ='${supplierId}' and s.auxiliary_materials_id = '${order}' ")
    List<FinancemxDTO> getAuxiliarymxOrder(Long supplierId,Long order);
    @Select("select s.food_id,s.arrival_num,s.unit_price,f.food_name,f.unit from canteens_batch_ordermx s ,canteens_food f where s.food_id =f.food_id and " +
            "s.supplier_id ='${supplierId}' and s.batch_order_id = '${order}' ")
    List<FinancemxDTO> getBatchMxOrder(Long supplierId,Long order);

    //供应商时间区间内的待支付和已支付的订单

    @Select("SELECT count(*)as totalNum FROM canteens_finance m  where   m.is_payment = '${status}'  ${supplierId}  ")
    FinanceDTO getFinance1(int status, String supplierId);
    @Select("SELECT count(*)as totalNum FROM canteens_finance m  where  m.is_payment = '${status}'  ${supplierId} ${bgeinDate} ${endDate} ")
    FinanceDTO getFinance(int status, String supplierId, String bgeinDate,String endDate);

    //库存表
    @Select("select A.food_id,A.food_num,A.unit_price, sum(A.food_num)as foodmxArrivalNum,SUM(A.unit_price*A.food_num)as foodmxTotalprice, " +
            "(select B.food_name from canteens_food B where B.food_id = A.food_id) as food_name, " +
            "(select B.unit from canteens_food B where B.food_id = A.food_id) as unit from canteens_foodmx A   GROUP BY A.food_id ")
    List<FinanceStatementDTO> selectfoodmx();

    //入库表
    @Select("select A.food_id,SUM(a.arrival_num)as putArrivalNum,SUM(a.arrival_num*A.unit_price)as putTotalprice, (select B.food_name from canteens_food B where B.food_id = A.food_id) as food_name, " +
            "(select B.unit from canteens_food B where B.food_id = A.food_id) as unit from canteens_auxiliary_materialsmx  A " +
            "LEFT JOIN  canteens_auxiliary_materi am on  DATE_FORMAT((SELECT DATE_ADD( curdate( ), INTERVAL - DAY ( curdate( ) ) + 1 DAY )),'%Y-%m-%d') <= DATE_FORMAT(am.create_date,'%Y-%m-%d') " +
            " and am.`status`>3 GROUP BY A.food_id ")
    List<FinanceStatementDTO> selectauxiliary();
    //出库表
    @Select("select  A.food_id,SUM(A.arrival_num)getTotalNum2,a.total_price as getTotalPrice2 from canteens_collarmx A " +
            " LEFT JOIN   canteens_collar B ON  DATE_FORMAT((SELECT DATE_ADD( curdate( ), INTERVAL - DAY ( curdate( ) ) + 1 DAY )),'%Y-%m-%d') <= DATE_FORMAT(B.create_date,'%Y-%m-%d')  " +
            "WHERE A.collar_id = B.collar_id  AND B.`status`>3 GROUP BY A.food_id " )
    List<FinanceStatementDTO> selectcollar();
    @Select("SELECT b.batch_order_name,A.* FROM canteens_finance A ,canteens_batch_order B where type = 4   and A.orderId =b.batch_order_id  ${canteenId} ")
    List<FinanceDTO> getBatchOrder(String canteenId);


    @Select("SELECT B.* FROM canteens_batch_order A , canteens_batch_ordermx B where B.batch_order_id = A.batch_order_id and  A.batch_order_id= ${batchOrderId}  ")
    List<BatchOrdermxDTO>getBatchOrdermx(Long batchOrderId);
    @Select("SELECT  sum(B.unit_price*B.purchase_num) as tatolMoney,(select c.supplier_name from canteens_supplier c where c.supplier_id =a.supplier_id) as supplier_name, " +
            " A.*  FROM canteens_batch_order A, canteens_batch_ordermx B where A.batch_order_id = b.batch_order_id  and A.batch_order_id = '${batchOrderId}'  ")
    BatchOrderDTO getTotalMoney(Long batchOrderId);
    @Select("UPDATE canteens_batch_order  set  status =2 where  batch_order_id = '${batchOrderId}'  ")
    void update1(Long batchOrderId);
}
